Problem Note 48014: The EXCEL engine ignores blanks when you use the WHERE clause
 |  |  |  |
Blank values are ignored in a WHERE clause when you are using the SQL procedure with an Excel database.
Sample code is shown below:
libname userinfo excel "c:\myworkbook.xlsx"
proc sql;
create table test as
select * from userinfo.'mysheet$'n
where myvar eq '20'x ;
quit;
The problem occurs because the EXCEL engine interprets blanks as NULLs in PROC SQL itself.
To circumvent the problem, change the expression to "is NULL," as shown below:
libname userinfo excel "c:\myworkbook.xlsx"
proc sql;
create table test as
select * from userinfo.'mysheet$'n
where myvar is NULL ;
quit;
You can also import the Excel worksheet into SAS® and use the original WHERE expression against the SAS data set. The SAS data set stores the character blank values as blanks rather than NULLs.
Operating System and Release Information
| Product Family | Product | System | SAS Release | |
| Reported | Fixed* | |||
| SAS System | SAS/ACCESS Interface to PC Files | Microsoft® Windows® for 64-Bit Itanium-based Systems | 9.2 TS2M0 | 9.4 TS1M0 |
| Microsoft Windows Server 2003 Datacenter 64-bit Edition | 9.2 TS2M0 | 9.4 TS1M0 | ||
| Microsoft Windows Server 2003 Enterprise 64-bit Edition | 9.2 TS2M0 | 9.4 TS1M0 | ||
| Microsoft Windows XP 64-bit Edition | 9.2 TS2M0 | 9.4 TS1M0 | ||
| Microsoft® Windows® for x64 | 9.2 TS2M0 | 9.4 TS1M0 | ||
| Microsoft Windows Server 2003 Datacenter Edition | 9.2 TS2M0 | 9.4 TS1M0 | ||
| Microsoft Windows Server 2003 Enterprise Edition | 9.2 TS2M0 | 9.4 TS1M0 | ||
| Microsoft Windows Server 2003 Standard Edition | 9.2 TS2M0 | 9.4 TS1M0 | ||
| Microsoft Windows Server 2003 for x64 | 9.2 TS2M0 | 9.4 TS1M0 | ||
| Microsoft Windows Server 2008 for x64 | 9.2 TS2M0 | 9.4 TS1M0 | ||
| Microsoft Windows XP Professional | 9.2 TS2M0 | 9.4 TS1M0 | ||
| Windows Vista | 9.2 TS2M0 | 9.4 TS1M0 | ||
| Windows Vista for x64 | 9.2 TS2M0 | 9.4 TS1M0 | ||
When you use a WHERE clause in PROC SQL with the EXCEL engine, the blanks are ignored as a value.
| Type: | Problem Note |
| Priority: | high |
| Date Modified: | 2013-08-06 13:29:48 |
| Date Created: | 2012-09-25 16:00:19 |